﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;


namespace SnmpForm.DbWrapper
{
    public class ExcelHelper
    {
        private Excel.Application _app = null;
        private Excel.Workbook _workbook = null;
        private Excel.Worksheet _worksheet = null;
        private Excel.Range _range = null;
        private bool _visible = true;
        private bool _exportTitle = false;
        private string _title = "";
        private object _missing = System.Reflection.Missing.Value;
        private object _fileName;
        /// <summary>
        /// 导出excel的标题
        /// </summary>
        public string Title
        {
            get { return _title; }
            set { _title = value; }
        }
        /// <summary>
        /// 当前表单
        /// </summary>
        public Excel.Worksheet CurrentWorkSheet
        {
            get { return _worksheet; }
            set { _worksheet = value; }
        }
        /// <summary>
        /// 当前Excel文档
        /// </summary>
        public Excel.Workbook CurrentWorkBook
        {
            get { return _workbook; }
            set { _workbook = value; }
        }
        /// <summary>
        /// 当前Excel应用
        /// </summary>
        public Excel.Application ExcelApp
        {
            get { return _app; }
            set { _app = value; }
        }
        /// <summary>
        /// Excel文档的可见性
        /// </summary>
        public bool ExcelVisible
        {
            get { return _visible; }
            set { _visible = value; }
        }
        /// <summary>
        /// 是否导出标题
        /// </summary>
        public bool ExprotTitle
        {
            get { return _exportTitle; }
            set { _exportTitle = value; }
        }

        /// <summary>
        /// 构造函数
        /// </summary>
        public ExcelHelper()
        {
             _app = new Excel.Application();
        }
        /// <summary>
        /// 构造函数，初始化Excel应用
        /// </summary>
        /// <param name="visible">Excel文档是否可见</param>
        public ExcelHelper(bool visible)
        {
            try
            {
                _app = new Excel.Application();
                _visible = visible;
                _app.Visible = _visible;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 析构函数
        /// </summary>
        ~ExcelHelper()
        {
            try
            {
                if (_app != null && _app.Visible == false)
                {
                    if (_workbook != null)
                    {
                        _workbook.Close();
                        _workbook = null;
                    }
                    _app.Quit();
                    _app = null;
        /*            System.Diagnostics.Process[] procs = System.Diagnostics.Process.GetProcessesByName("excel");
                    foreach (System.Diagnostics.Process pro in procs)
                    {
                        pro.Kill();//没有更好的方法,只有杀掉进程  
                    }
          */
                    GC.Collect();
               }
            }
            catch
            {
            }
        }
        /*
         * 手动释放 
         */
        public void Dispose()
        {
            try
            {
                if (_app != null && _app.Visible == false)
                {
                    _worksheet = null;
                    if (_workbook != null)
                    {
                        _workbook.Close();
                        _workbook = null;
                    }
                    _app.Quit();
                    _app = null;
     /*               System.Diagnostics.Process[] procs = System.Diagnostics.Process.GetProcessesByName("excel");
                    foreach (System.Diagnostics.Process pro in procs)
                    {
                        pro.Kill();//没有更好的方法,只有杀掉进程  
                    }
           */
                    GC.Collect();
                }
            }
            catch
            {
            }
        }

        /// <summary>
        /// 初始化app属性
        /// </summary>
        public void InitApp()
        {
            try
            {
                if (_app == null)
                    _app = new Excel.Application();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 初始化wookbook属性
        /// </summary>
        public void InitBook()
        {
            if (_app == null)
            {
                InitApp();
            }
            try
            {
                if (_workbook == null)
                    _workbook = _app.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
            }
            catch (Exception ex)
            {

                throw ex;
            }

        }
        /// <summary>
        /// 创建Excel文档
        /// </summary>
        public Excel.Workbook CreateBook()
        {
            Excel.Workbook book = null;
            try
            {
                book = _app.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
            }
            catch (Exception ex)
            {

                throw ex;
            }
            return book;
        }
        /// <summary>
        /// 创建Excel文档
        /// </summary>
        /// <param name="templete">Excel文档创建模板</param>
        /// <returns></returns>
        public Excel.Workbook CreateBook(object templete)
        {
            Excel.Workbook book = null;
            try
            {
                book = _app.Workbooks.Add(templete);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return book;
        }
        /// <summary>
        /// 打开Excel
        /// </summary>
        /// <param name="fileName">文件名</param>
        public Excel.Workbook OpenBook(string fileName)
        {
            if (_app == null)
            {
                InitApp();
            }
            Excel.Workbook book = null;
            _fileName = fileName;
            try
            {
                book = _app.Workbooks.Open(fileName, _missing, _missing, _missing,
                    _missing, _missing, _missing, _missing, _missing, _missing, _missing,
                    _missing, _missing, _missing, _missing);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return book;
        }
        /// <summary>
        /// 获得表单对象
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="sheetIndex"></param>
        /// <returns></returns>
        public Excel.Worksheet GetSheet(Excel.Workbook workbook, int sheetIndex)
        {
            Excel.Worksheet sheet = null;
            try
            {
                sheet = (Excel.Worksheet)workbook.Sheets[sheetIndex];
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return sheet;
        }
        /// <summary>
        /// 获得表单对象
        /// </summary>
        /// <param name="sheetIndex"></param>
        /// <returns></returns>
        public Excel.Worksheet GetSheet(int sheetIndex)
        {
            if (_workbook == null)
            {
                InitBook();
            }
            Excel.Worksheet sheet = null;
            try
            {
                sheet = (Excel.Worksheet)_workbook.Sheets[sheetIndex];
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return sheet;
        }

        /*
         *  功能：
         *      list数据写入excel表
         * 参数:
         *      liDetail:数据源   conlumnName:列名，针对list元素中字典key名   columnDiscription：excle表中显示名
         * 返回值：
         *      bool
         */
        public bool ListToExcel(List<object> lsDetail, string[] columnName, string[] columnDiscription)
        {
 /*          
            if (lsDetail == null)
            {
                return false;
            }
 */          
            if (columnName.Length == 0 || columnName.Length != columnDiscription.Length)
            {
                return false;
            }

            try
            {
                //设置标题
                int titleRow = 0;
                int colsLen = columnName.Length;
                _worksheet = GetSheet(1);
                _range = _worksheet.get_Range(_worksheet.Cells[1, 1], _worksheet.Cells[1, colsLen]);
                if (_exportTitle)
                {
                    titleRow = 1;
                    _range.Merge(0);
                    _range.Value2 = _title;
                    _range.NumberFormat = "@";
                    _range.Font.Size = 20;
                    _range.Font.Bold = true;
                    _range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                    _range.RowHeight = 40;
                }
                //设置表头
                _range = _worksheet.get_Range(_worksheet.Cells[1 + titleRow, 1], _worksheet.Cells[1 + titleRow, colsLen]);
                _range.Font.Size = 10;
                _range.Font.Bold = true;
                _range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                _range.NumberFormat = "@";
                //_range.ColumnWidth = 10;
                _range.Borders.ColorIndex = 1;
                for (int i = 1; i <= colsLen; i++)
                {
                    _worksheet.Cells[1 + titleRow, i] = columnDiscription[i - 1];
                    //_range = (Excel.Range)_worksheet.Cells[1, i];
                    //_range.Value2 = columns;
                }
                //只导出模板
                if (lsDetail == null)
                {
                    _range = _worksheet.get_Range(_worksheet.Cells[1, 1], _worksheet.Cells[1, colsLen]);
                    _range.EntireColumn.AutoFit();
                    return true;
                }
                //导出数据
                int rowsLen = 2 + titleRow;
                _range = _worksheet.get_Range(_worksheet.Cells[2 + titleRow, 1], _worksheet.Cells[2 + lsDetail.Count - 1, colsLen]);
                _range.NumberFormat = "@";
                _range.Font.Size = 10;
                _range.Borders.ColorIndex = 1;

                foreach (object o in lsDetail)
                {
                    Dictionary<string, object> entity = (Dictionary<string, object>)o;
                    for (int i = 1; i <= colsLen; i++)
                    {
                        if (i == 5)
                        {//snmp密码加密存储
                            try
                            {
                                _worksheet.Cells[rowsLen, i] = BusinessWrapper.AES.AESDecrypt(entity[columnName[i - 1]].ToString());
                            }
                            catch
                            {
                                _worksheet.Cells[rowsLen, i] = "";
                            }
                        }
                        else
                        {
                            _worksheet.Cells[rowsLen, i] = entity[columnName[i - 1]];
                        }
                    }
                    rowsLen++;

                }
                _range = _worksheet.get_Range(_worksheet.Cells[1, 1], _worksheet.Cells[1, colsLen]);
                _range.EntireColumn.AutoFit();
            }
            catch (Exception ex)
            {
            //    _worksheet = GetSheet(1);
                Console.WriteLine(ex.Message.ToString());
                return false;
            }
            return true;
        }

        /*
         * 功能：
         *      将list数据导出到excle文件中
         * 参数:
         *      liDetail:数据源   conlumnName:列名，针对list元素中字典key名   columnDiscription：excle表中显示名
         * 返回值：
         *      bool
         */
        public bool ListToExcelSaved(List<object> lsDetail, string[] columnName, string[] columnDiscription)
        {

            SaveFileDialog saveDialog = new SaveFileDialog();
            saveDialog.InitialDirectory = Application.ExecutablePath.ToString();
            saveDialog.FileName = "APManager.xlsx";
            saveDialog.Filter = "Excel文件(*.xls)|*.xls";
            saveDialog.FilterIndex = 1;
            saveDialog.RestoreDirectory = true;
            try
            {
                if (saveDialog.ShowDialog() == DialogResult.OK)
                {
                    InitBook();
                    if (ListToExcel(lsDetail, columnName, columnDiscription) == false)
                    {
                        MessageBox.Show("保存失败");
                        _workbook.Close();
                        _workbook = null;
                        return false;
                    }
                    _workbook.SaveAs(saveDialog.FileName);
                    _workbook.Close();
                    _workbook = null;
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message.ToString());
                return false;
            }
            return true;
        }

        /*
         * 功能：
         *      读取excel表内容存放于list对象中
         *  参数：
         *      filename：excel文件名；  columnDict：针对list元素中字典key名
         */
        public List<object> ExcelToList(string[] columnDict)
        {
            int titleRow = 1;
            //从模板表格的表头获取列数
            int columnNum = 0;
            while(true)
            {
                if (((Excel.Range)_worksheet.Cells[titleRow, columnNum+1]).Text.ToString() == "")
                {
                    break;
                }
                columnNum++;
            }
            if (columnNum != columnDict.Length)
            {
                return null;
            }

            int row = titleRow+1;
            List<object> list = new List<object>();
            while(true)
            {
                if (((Excel.Range)_worksheet.Cells[row, 1]).Text.ToString() == "")
                {
                    break;
                }
                Dictionary<string, object> entity = new Dictionary<string, object>();
                for (int column= 1;column <= columnNum; column++)
                {
                    if (column == 5)
                    {
                        try
                        {
                            string cipher = BusinessWrapper.AES.AESEncrypt(((Excel.Range)_worksheet.Cells[row, column]).Text.ToString());
                            entity.Add(columnDict[column - 1], cipher);
                        }
                        catch
                        {
                        }
                    }
                    else
                    {
                        entity.Add(columnDict[column - 1], ((Excel.Range)_worksheet.Cells[row, column]).Text.ToString());
                    }
                }
                list.Add(entity);
                row++;
            }
            return list;
        }


        public List<object> ReadExcleToList(string[] column)
        {
            OpenFileDialog openDialog = new OpenFileDialog();
            openDialog.InitialDirectory = Application.ExecutablePath.ToString();
            openDialog.FileName = "APManager.xlsx";
            openDialog.Filter = "Excel文件(*.xlsx)|*.xlsx|所有文件（*.*)|*.*";
            openDialog.FilterIndex = 1;
            openDialog.RestoreDirectory = true;
            try
            {
                if (openDialog.ShowDialog() == DialogResult.OK)
                {
                    _workbook = OpenBook(openDialog.FileName);
                    _worksheet = GetSheet(_workbook, 1);
                    return ExcelToList(column);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return null;
        }

         
        /// <summary>
        /// 查找Excel文档中的表单并设定其为当前表单，当前表单赋值给CurrentSheet属性
        /// </summary>
        /// <param name="worksheetName">表单名称</param>
        /// <returns>找到时返回真</returns>
        public bool FindExcelWorksheet(Excel.Workbook workbook, string worksheetName)
        {
            bool ATP_SHEET_FOUND = false;

            if (workbook != null)
            {
                for (int i = 1; i <= workbook.Worksheets.Count; i++)
                {
                    _worksheet = (Excel.Worksheet)workbook.Worksheets.get_Item((object)i);
                    if (_worksheet.Name.Equals(worksheetName))
                    {
                        _worksheet.Activate();
                        ATP_SHEET_FOUND = true;
                        return ATP_SHEET_FOUND;
                    }
                }
            }
            return ATP_SHEET_FOUND;
        }
        /// <summary>
        /// 关闭Excel文档
        /// </summary>
        public void CloseFile()
        {
            _workbook.Close(false, _fileName, false);
        }
        /// <summary>
        /// 终止Excel程序
        /// </summary>
        public void StopExcel()
        {
            if (this._app != null)
            {
                System.Diagnostics.Process[] pProcess;
                pProcess = System.Diagnostics.Process.GetProcessesByName("Excel");
                pProcess[0].Kill();
            }
        }
    }
}
   